from typing import Optional, List, Dict, Any, Tuple
from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, desc, asc, func, text
from datetime import datetime, timedelta
import logging

from app.domains.models import WorkOrder, WorkOrderComment, WorkOrderAttachment, Device, User
from app.domains.work_order.schemas import (
    WorkOrderCreate, WorkOrderUpdate, WorkOrderListQuery, WorkOrderAssign,
    WorkOrderStatusUpdate, WorkOrderComment as WorkOrderCommentSchema,
    WorkOrderBatchOperation
)
from app.shared.exceptions.base import (
    NotFoundException, ConflictException, ValidationException, BusinessException
)
from app.shared.constants.status_codes import BusinessCode
from app.shared.constants.enums import WorkOrderStatus, WorkOrderPriority, WorkOrderType, UserRole
from app.shared.utils.datetime import utc_now
from app.shared.utils.formatter import generate_work_order_number

logger = logging.getLogger(__name__)


class WorkOrderRepository:
    """工单数据访问层"""
    
    def __init__(self, db: Session):
        self.db = db
    
    def get_by_id(self, work_order_id: int) -> Optional[WorkOrder]:
        """根据ID获取工单"""
        return self.db.query(WorkOrder).filter(WorkOrder.id == work_order_id).first()
    
    def get_by_number(self, work_order_number: str) -> Optional[WorkOrder]:
        """根据工单编号获取工单"""
        return self.db.query(WorkOrder).filter(WorkOrder.work_order_number == work_order_number).first()
    
    def create(self, work_order_data: WorkOrderCreate, reporter_id: int) -> WorkOrder:
        """创建工单"""
        work_order_number = generate_work_order_number()
        
        work_order = WorkOrder(
            work_order_number=work_order_number,
            title=work_order_data.title,
            description=work_order_data.description,
            device_id=work_order_data.device_id,
            reporter_id=reporter_id,
            priority=work_order_data.priority,
            work_order_type=work_order_data.work_order_type,
            location=work_order_data.location,
            estimated_duration=work_order_data.estimated_duration,
            required_skills=work_order_data.required_skills,
            materials_needed=work_order_data.materials_needed,
            status=WorkOrderStatus.PENDING,
            created_at=utc_now(),
            updated_at=utc_now()
        )
        
        self.db.add(work_order)
        self.db.commit()
        self.db.refresh(work_order)
        
        logger.info(f"✅ 工单创建成功: {work_order.work_order_number} - {work_order.title}")
        return work_order
    
    def update(self, work_order: WorkOrder, work_order_data: WorkOrderUpdate) -> WorkOrder:
        """更新工单信息"""
        update_data = work_order_data.dict(exclude_unset=True)
        
        for field, value in update_data.items():
            setattr(work_order, field, value)
        
        work_order.updated_at = utc_now()
        
        self.db.commit()
        self.db.refresh(work_order)
        
        logger.info(f"✅ 工单信息更新成功: {work_order.work_order_number}")
        return work_order
    
    def assign(self, work_order: WorkOrder, assignee_id: int, notes: Optional[str] = None, due_date: Optional[datetime] = None) -> WorkOrder:
        """分配工单"""
        work_order.assignee_id = assignee_id
        work_order.assigned_at = utc_now()
        work_order.due_date = due_date
        work_order.updated_at = utc_now()
        
        if work_order.status == WorkOrderStatus.PENDING:
            work_order.status = WorkOrderStatus.ASSIGNED
        
        # 记录状态变更
        self._add_status_history(work_order, WorkOrderStatus.ASSIGNED, notes)
        
        self.db.commit()
        self.db.refresh(work_order)
        
        logger.info(f"✅ 工单分配成功: {work_order.work_order_number} -> 用户ID {assignee_id}")
        return work_order
    
    def update_status(self, work_order: WorkOrder, status_data: WorkOrderStatusUpdate) -> WorkOrder:
        """更新工单状态"""
        old_status = work_order.status
        work_order.status = status_data.status
        work_order.updated_at = utc_now()
        
        # 根据状态设置相应时间戳
        if status_data.status == WorkOrderStatus.IN_PROGRESS and old_status != WorkOrderStatus.IN_PROGRESS:
            work_order.started_at = utc_now()
        elif status_data.status == WorkOrderStatus.COMPLETED:
            work_order.completed_at = utc_now()
            if status_data.actual_duration:
                work_order.actual_duration = status_data.actual_duration
            if status_data.completion_notes:
                work_order.completion_notes = status_data.completion_notes
            if status_data.materials_used:
                work_order.materials_used = status_data.materials_used
        
        # 记录状态变更
        self._add_status_history(work_order, status_data.status, status_data.notes)
        
        self.db.commit()
        self.db.refresh(work_order)
        
        logger.info(f"✅ 工单状态更新: {work_order.work_order_number} {old_status} -> {status_data.status}")
        return work_order
    
    def _add_status_history(self, work_order: WorkOrder, status: WorkOrderStatus, notes: Optional[str] = None):
        """添加状态变更历史"""
        history = {
            'status': status.value,
            'timestamp': utc_now().isoformat(),
            'notes': notes
        }
        
        if not work_order.status_history:
            work_order.status_history = []
        work_order.status_history.append(history)
    
    def get_list(self, query: WorkOrderListQuery) -> Tuple[List[WorkOrder], int]:
        """获取工单列表"""
        q = self.db.query(WorkOrder).join(Device, WorkOrder.device_id == Device.id)
        
        # 搜索条件
        if query.search:
            search_term = f"%{query.search}%"
            q = q.filter(or_(
                WorkOrder.work_order_number.like(search_term),
                WorkOrder.title.like(search_term),
                WorkOrder.description.like(search_term),
                Device.device_code.like(search_term),
                Device.device_name.like(search_term)
            ))
        
        # 状态筛选
        if query.status:
            q = q.filter(WorkOrder.status == query.status)
        
        # 优先级筛选
        if query.priority:
            q = q.filter(WorkOrder.priority == query.priority)
        
        # 工单类型筛选
        if query.work_order_type:
            q = q.filter(WorkOrder.work_order_type == query.work_order_type)
        
        # 指派人员筛选
        if query.assignee_id:
            q = q.filter(WorkOrder.assignee_id == query.assignee_id)
        
        # 报告人筛选
        if query.reporter_id:
            q = q.filter(WorkOrder.reporter_id == query.reporter_id)
        
        # 设备筛选
        if query.device_id:
            q = q.filter(WorkOrder.device_id == query.device_id)
        
        # 位置筛选
        if query.location:
            q = q.filter(WorkOrder.location.like(f"%{query.location}%"))
        
        # 日期范围筛选
        if query.start_date:
            q = q.filter(WorkOrder.created_at >= query.start_date)
        
        if query.end_date:
            q = q.filter(WorkOrder.created_at <= query.end_date)
        
        # 超期工单筛选
        if query.overdue_only:
            now = utc_now()
            q = q.filter(
                and_(
                    WorkOrder.due_date < now,
                    WorkOrder.status.in_([
                        WorkOrderStatus.PENDING,
                        WorkOrderStatus.ASSIGNED,
                        WorkOrderStatus.IN_PROGRESS
                    ])
                )
            )
        
        # 排序
        sort_column = getattr(WorkOrder, query.sort_by, WorkOrder.created_at)
        if query.sort_order == "desc":
            q = q.order_by(desc(sort_column))
        else:
            q = q.order_by(asc(sort_column))
        
        # 获取总数
        total = q.count()
        
        # 分页
        offset = (query.page - 1) * query.page_size
        work_orders = q.offset(offset).limit(query.page_size).all()
        
        return work_orders, total
    
    def delete(self, work_order: WorkOrder) -> bool:
        """删除工单"""
        try:
            # 删除相关评论和附件
            self.db.query(WorkOrderComment).filter(WorkOrderComment.work_order_id == work_order.id).delete()
            self.db.query(WorkOrderAttachment).filter(WorkOrderAttachment.work_order_id == work_order.id).delete()
            
            self.db.delete(work_order)
            self.db.commit()
            
            logger.info(f"✅ 工单删除成功: {work_order.work_order_number}")
            return True
        except Exception as e:
            logger.error(f"❌ 工单删除失败: {e}")
            self.db.rollback()
            return False
    
    def get_by_ids(self, work_order_ids: List[int]) -> List[WorkOrder]:
        """根据ID列表获取工单"""
        return self.db.query(WorkOrder).filter(WorkOrder.id.in_(work_order_ids)).all()
    
    def get_statistics(self) -> Dict[str, Any]:
        """获取工单统计数据"""
        # 状态统计
        status_stats = self.db.query(
            WorkOrder.status,
            func.count(WorkOrder.id).label('count')
        ).group_by(WorkOrder.status).all()
        
        # 总工单数
        total_count = sum(stat.count for stat in status_stats)
        
        # 各状态工单数
        pending_count = sum(stat.count for stat in status_stats if stat.status == WorkOrderStatus.PENDING)
        in_progress_count = sum(stat.count for stat in status_stats if stat.status == WorkOrderStatus.IN_PROGRESS)
        completed_count = sum(stat.count for stat in status_stats if stat.status == WorkOrderStatus.COMPLETED)
        
        # 超期工单数
        now = utc_now()
        overdue_count = self.db.query(WorkOrder).filter(
            and_(
                WorkOrder.due_date < now,
                WorkOrder.status.in_([
                    WorkOrderStatus.PENDING,
                    WorkOrderStatus.ASSIGNED,
                    WorkOrderStatus.IN_PROGRESS
                ])
            )
        ).count()
        
        # 平均处理时长（已完成的工单）
        avg_resolution_time = self.db.query(func.avg(WorkOrder.actual_duration)).filter(
            and_(
                WorkOrder.status == WorkOrderStatus.COMPLETED,
                WorkOrder.actual_duration.isnot(None)
            )
        ).scalar()
        
        # 完成率
        completion_rate = (completed_count / total_count * 100) if total_count > 0 else 0
        
        return {
            'total_count': total_count,
            'pending_count': pending_count,
            'in_progress_count': in_progress_count,
            'completed_count': completed_count,
            'overdue_count': overdue_count,
            'avg_resolution_time': round(float(avg_resolution_time or 0) / 60, 2),  # 转换为小时
            'completion_rate': round(completion_rate, 2)
        }


class WorkOrderService:
    """工单业务逻辑层"""
    
    def __init__(self, db: Session):
        self.db = db
        self.repository = WorkOrderRepository(db)
    
    def create_work_order(self, work_order_data: WorkOrderCreate, reporter_id: int) -> WorkOrder:
        """创建工单"""
        # 验证设备是否存在
        device = self.db.query(Device).filter(Device.id == work_order_data.device_id).first()
        if not device:
            raise NotFoundException("设备", work_order_data.device_id)
        
        # 验证报告人是否存在
        reporter = self.db.query(User).filter(User.id == reporter_id).first()
        if not reporter:
            raise NotFoundException("用户", reporter_id)
        
        return self.repository.create(work_order_data, reporter_id)
    
    def get_work_order_by_id(self, work_order_id: int) -> WorkOrder:
        """根据ID获取工单"""
        work_order = self.repository.get_by_id(work_order_id)
        if not work_order:
            raise NotFoundException("工单", work_order_id)
        return work_order
    
    def update_work_order(self, work_order_id: int, work_order_data: WorkOrderUpdate) -> WorkOrder:
        """更新工单信息"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        # 只有待处理和已分配的工单可以修改基本信息
        if work_order.status not in [WorkOrderStatus.PENDING, WorkOrderStatus.ASSIGNED]:
            raise BusinessException(
                BusinessCode.OPERATION_FAILED,
                "只有待处理和已分配的工单可以修改基本信息"
            )
        
        return self.repository.update(work_order, work_order_data)
    
    def assign_work_order(self, work_order_id: int, assign_data: WorkOrderAssign) -> WorkOrder:
        """分配工单"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        # 验证指派人员是否存在且有相应权限
        assignee = self.db.query(User).filter(User.id == assign_data.assignee_id).first()
        if not assignee:
            raise NotFoundException("用户", assign_data.assignee_id)
        
        if assignee.role not in [UserRole.TECHNICIAN, UserRole.MANAGER, UserRole.ADMIN]:
            raise ValidationException("只能分配给技术员、经理或管理员")
        
        # 只有待处理的工单可以分配
        if work_order.status != WorkOrderStatus.PENDING:
            raise BusinessException(
                BusinessCode.OPERATION_FAILED,
                "只有待处理的工单可以分配"
            )
        
        return self.repository.assign(
            work_order,
            assign_data.assignee_id,
            assign_data.notes,
            assign_data.due_date
        )
    
    def update_work_order_status(self, work_order_id: int, status_data: WorkOrderStatusUpdate, user_id: int) -> WorkOrder:
        """更新工单状态"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        # 验证状态转换的合法性
        self._validate_status_transition(work_order.status, status_data.status)
        
        # 验证权限：只有指派人员或管理员可以更新状态
        user = self.db.query(User).filter(User.id == user_id).first()
        if not user:
            raise NotFoundException("用户", user_id)
        
        if user.role not in [UserRole.ADMIN, UserRole.MANAGER] and work_order.assignee_id != user_id:
            raise ValidationException("只有指派人员或管理员可以更新工单状态")
        
        return self.repository.update_status(work_order, status_data)
    
    def _validate_status_transition(self, current_status: WorkOrderStatus, new_status: WorkOrderStatus):
        """验证状态转换的合法性"""
        valid_transitions = {
            WorkOrderStatus.PENDING: [WorkOrderStatus.ASSIGNED, WorkOrderStatus.CANCELLED],
            WorkOrderStatus.ASSIGNED: [WorkOrderStatus.IN_PROGRESS, WorkOrderStatus.CANCELLED],
            WorkOrderStatus.IN_PROGRESS: [WorkOrderStatus.COMPLETED, WorkOrderStatus.ON_HOLD, WorkOrderStatus.CANCELLED],
            WorkOrderStatus.ON_HOLD: [WorkOrderStatus.IN_PROGRESS, WorkOrderStatus.CANCELLED],
            WorkOrderStatus.COMPLETED: [],  # 已完成的工单不能再转换状态
            WorkOrderStatus.CANCELLED: []   # 已取消的工单不能再转换状态
        }
        
        if new_status not in valid_transitions.get(current_status, []):
            raise ValidationException(f"工单状态不能从 {current_status.value} 转换为 {new_status.value}")
    
    def delete_work_order(self, work_order_id: int) -> bool:
        """删除工单"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        # 只有待处理状态的工单可以删除
        if work_order.status != WorkOrderStatus.PENDING:
            raise BusinessException(
                BusinessCode.OPERATION_FAILED,
                "只有待处理状态的工单可以删除"
            )
        
        return self.repository.delete(work_order)
    
    def get_work_order_list(self, query: WorkOrderListQuery) -> Tuple[List[WorkOrder], int]:
        """获取工单列表"""
        return self.repository.get_list(query)
    
    def get_work_order_detail(self, work_order_id: int) -> Dict[str, Any]:
        """获取工单详细信息"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        # 获取评论
        comments = self.db.query(WorkOrderComment).filter(
            WorkOrderComment.work_order_id == work_order_id
        ).order_by(desc(WorkOrderComment.created_at)).all()
        
        # 获取附件
        attachments = self.db.query(WorkOrderAttachment).filter(
            WorkOrderAttachment.work_order_id == work_order_id
        ).all()
        
        return {
            "work_order": work_order,
            "comments": comments,
            "attachments": attachments
        }
    
    def add_comment(self, work_order_id: int, comment_data: WorkOrderCommentSchema, user_id: int) -> WorkOrderComment:
        """添加工单评论"""
        work_order = self.get_work_order_by_id(work_order_id)
        
        comment = WorkOrderComment(
            work_order_id=work_order_id,
            user_id=user_id,
            content=comment_data.content,
            is_internal=comment_data.is_internal,
            created_at=utc_now()
        )
        
        self.db.add(comment)
        self.db.commit()
        self.db.refresh(comment)
        
        logger.info(f"✅ 工单评论添加成功: {work_order.work_order_number}")
        return comment
    
    def batch_operation(self, operation_data: WorkOrderBatchOperation, user_id: int) -> Dict[str, Any]:
        """批量操作工单"""
        work_orders = self.repository.get_by_ids(operation_data.work_order_ids)
        
        if len(work_orders) != len(operation_data.work_order_ids):
            found_ids = [wo.id for wo in work_orders]
            missing_ids = [id for id in operation_data.work_order_ids if id not in found_ids]
            raise ValidationException(f"工单不存在: {missing_ids}")
        
        results = {
            "success": [],
            "failed": [],
            "total": len(work_orders)
        }
        
        for work_order in work_orders:
            try:
                if operation_data.operation == "assign":
                    assignee_id = operation_data.params.get("assignee_id")
                    if not assignee_id:
                        raise ValidationException("缺少指派人员ID")
                    
                    self.repository.assign(work_order, assignee_id)
                    results["success"].append(work_order.id)
                    
                elif operation_data.operation == "update_status":
                    status = WorkOrderStatus(operation_data.params.get("status"))
                    notes = operation_data.params.get("notes")
                    status_data = WorkOrderStatusUpdate(status=status, notes=notes)
                    
                    self._validate_status_transition(work_order.status, status)
                    self.repository.update_status(work_order, status_data)
                    results["success"].append(work_order.id)
                    
                elif operation_data.operation == "delete":
                    if work_order.status == WorkOrderStatus.PENDING:
                        self.repository.delete(work_order)
                        results["success"].append(work_order.id)
                    else:
                        results["failed"].append({
                            "work_order_id": work_order.id,
                            "reason": "只能删除待处理状态的工单"
                        })
                        
                else:
                    results["failed"].append({
                        "work_order_id": work_order.id,
                        "reason": f"不支持的操作: {operation_data.operation}"
                    })
                    
            except Exception as e:
                results["failed"].append({
                    "work_order_id": work_order.id,
                    "reason": str(e)
                })
        
        logger.info(f"✅ 批量操作完成: 成功 {len(results['success'])}, 失败 {len(results['failed'])}")
        return results
    
    def get_statistics(self) -> Dict[str, Any]:
        """获取工单统计数据"""
        return self.repository.get_statistics()